Docker 搭建 MySQL 主从

Docker 启动, 并开启 binlog

启动服务

version: "3"
services:
    mysql_ac:
      container_name: mysql_5.7_docker
      image: nexus-docker.mycyclone.com/testdev/mysql:5.7.34
      restart: always
      ports:
        - 33008:3306
      command:
        [
          "mysqld",
          "--character-set-server=utf8mb4",
          "--collation-server=utf8mb4_unicode_ci",
        ]
      environment:
          MYSQL_ROOT_PASSWORD: cyclone
      volumes:
        - ./etc/mysql:/etc/mysql
        - ./opt/mysql:/var/lib/mysql

1	docker-compose up -d

开启 binlog

1	docker exec mysql_5.7_docker bash -c "echo 'log-bin=/var/lib/mysql/mysql-bin' >> /etc/mysql/mysql.conf.d/mysqld.cnf"

1	docker exec mysql_5.7_docker bash -c "echo 'server-id=123454' >> /etc/mysql/mysql.conf.d/mysqld.cnf"

1	docker restart mysql_5.7_docker

操作日志文件

查看开启状态

1	show variables like '%log_bin%';

以下格式就为开启了 binlog

-- log_bin	ON
-- log_bin_basename	/var/lib/mysql/mysql-bin
-- log_bin_index	/var/lib/mysql/mysql-bin.index
-- log_bin_trust_function_creators	OFF
-- log_bin_use_v1_row_events	OFF
-- sql_log_bin	ON

查看 binlog 日志文件

1	show BINARY logs;

1	-- mysql-bin.000001 812

查看正在写入的文件

1	show master status

清空 binlog

清空后数据还在, binlog 大小不会为0。

1	reset master

查看日志内容

使用 mysqlbinlog

mysqlbinlog 是 mysql 自带的查看 binlog 的工具，需要在安装 mysql 的机器上执行，并非在 mysql 命令行中。

另外，执行路径需要到日志文件路径下才能执行。

ROW 模式生成的 sql 需要 base 解码才能看到内容, --base64-output=decode-rows -v 的作用即为解码

1	mysqlbinlog mysql-bin.000001 --base64-output=decode-rows -v

使用 mysql 命令查看

1	show binlog events in 'mysql-bin.000001'

查看当前 binlog 日志格式

1	show variables like 'binlog_format'

一主多从搭建

docker-compose.yaml 文件如下， mysql_master_1设置在 33008, mysql_slave_1 在 33009，mysql_slave_2 在 33010

version: "3"
services:
    mysql_master_1:
      container_name: mysql_master_1
      image: nexus-docker.mycyclone.com/testdev/mysql:5.7.34
      restart: always
      ports:
        - 33008:3306
      command:
        [
          "mysqld",
          "--character-set-server=utf8mb4",
          "--collation-server=utf8mb4_unicode_ci",
        ]
      environment:
          MYSQL_ROOT_PASSWORD: 123456
    
    mysql_slave_1:
      container_name: mysql_slave_1
      image: nexus-docker.mycyclone.com/testdev/mysql:5.7.34
      restart: always
      ports:
        - 33009:3306
      command:
        [
          "mysqld",
          "--character-set-server=utf8mb4",
          "--collation-server=utf8mb4_unicode_ci",
        ]
      environment:
          MYSQL_ROOT_PASSWORD: 123456
    
    mysql_slave_2:
      container_name: mysql_slave_2
      image: nexus-docker.mycyclone.com/testdev/mysql:5.7.34
      restart: always
      ports:
        - 33010:3306
      command:
        [
          "mysqld",
          "--character-set-server=utf8mb4",
          "--collation-server=utf8mb4_unicode_ci",
        ]
      environment:
          MYSQL_ROOT_PASSWORD: 123456

开启主库 binlog

1	docker exec mysql_master_1 bash -c "echo 'log-bin=/var/lib/mysql/mysql-bin' >> /etc/mysql/mysql.conf.d/mysqld.cnf"

1	docker exec mysql_master_1 bash -c "echo 'server-id=123454' >> /etc/mysql/mysql.conf.d/mysqld.cnf"

1	docker restart mysql_master_1

创建同步用户

1 2	CREATE USER 'sync_user'@'%' IDENTIFIED BY '123456'; GRANT REPLICATION SLAVE ON . TO 'sync_user'@'%';

创建表插入数据

CREATE TABLE `tb_person` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR ( 36 ) NOT NULL,
	`address` VARCHAR ( 36 ) NOT NULL DEFAULT '',
	`sex` VARCHAR ( 12 ) NOT NULL DEFAULT 'Man',
	`other` VARCHAR ( 256 ) NOT NULL,
	PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 0 DEFAULT CHARSET = utf8;
             
insert into tb_person  set name="name1", address="beijing", sex="man", other="nothing";
insert into tb_person  set name="name2", address="beijing", sex="man", other="nothing";
insert into tb_person  set name="name3", address="beijing", sex="man", other="nothing";
insert into tb_person  set name="name4", address="beijing", sex="man", other="nothing";

Slave 配置

设置 server-id

1	docker exec mysql_slave_1 bash -c "echo 'server-id=123454' >> /etc/mysql/mysql.conf.d/mysqld.cnf"

1	docker restart mysql_slave_1

连接 master

change master to
master_host='mysql_master_1',
master_user='sync_user',
master_port=3306,
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=123;

查看 slave 状态

1	show slave status\G

启动复制

1	start slave;

再次查看 slave 状态

查看两个库数据是否一致

判断主从是否延迟

首先看 Relay_Master_Log_File 和 Maser_Log_File 是否有差异;
如果Relay_Master_Log_File 和 Master_Log_File 是一样的话，再来看Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差异，对比SQL线程比IO线程慢了多少个binlog事件
如果Relay_Master_Log_File 和 Master_Log_File 不一样，那说明延迟可能较大，需要从MASTER上取得binlog status，判断当前的binlog和MASTER上的差距
如果以上都不能发现问题，可使用 pt_heartbeat工具来监控主备复制的延迟。

星尘